package core2.ch04database;
import java.sql.*;
import java.util.Properties;

public class ExampleApplication {
	private Connection con;

	private Driver driver;

	private final String TABLE_NAME = "TEST_TABLE";

	public void run() {
		try {
			loadJDBCDriver();
			connectToDatabase();
			populateTable();
			displayTable();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private boolean loadJDBCDriver() throws SQLException {
		try {
			driver = (Driver) Class.forName("com.jnetdirect.jsql.JSQLDriver")
					.newInstance();
			log("Loaded driver OK");
			return true;
		} catch (Exception e) {
			log("Cannot load the driver, reason:" + e.toString()
					+ "Probably the JSQLConnect.jar is not in the classpath");
			throw new SQLException(e.toString());
		}
	}

	private boolean connectToDatabase() throws SQLException {
		String connectStr = "jdbc:JSQLConnect://localhost:1433/database=osworkflow_app/user=sa/password=sa";
		log("Starting connection to " + connectStr);
		try {
			con = driver.connect(connectStr, new Properties());
			log("Connected to database OK");
			return true;
		} catch (SQLException e) {
			log("Cannot connect to database, reason:" + e.toString());
			throw e;
		}
	}

	private void populateTable() throws SQLException {
		log("Creating table ..");
		Statement s = con.createStatement();
		try {
			s.executeUpdate("DROP TABLE " + TABLE_NAME);
		} catch (SQLException e) {
		}

		s.executeUpdate("CREATE TABLE " + TABLE_NAME
				+ " (COL1 INTEGER, COL2 VARCHAR(10))");

		log("Populating table rows ..");
		for (int i = 0; i < 10; i++) {
			s.executeUpdate("INSERT INTO " + TABLE_NAME
					+ " (COL1, COL2) VALUES (" + i + ", 'VALUE" + i + "')");
		}
	}

	private void displayTable() throws SQLException {
		log("Displaying table rows ..");
		Statement s = con.createStatement();
		ResultSet rs = s.executeQuery("SELECT * FROM " + TABLE_NAME);
		ResultSetMetaData rsMeta = rs.getMetaData();
		int columns = rsMeta.getColumnCount();
		for (int i = 0; i < columns; i++)
			log("ColumnName " + i + " " + rsMeta.getColumnName(i + 1));
		int row = 0;
		while (rs.next()) {
			String rowData = "";
			for (int i = 0; i < columns; i++) {
				rowData += rs.getString(i + 1) + "   ";
			}
			log("RowData " + row + ":" + rowData);
			row++;
		}
		log("Total rows:" + row);
	}

	private void log(String s) {
		System.out.println("Log:" + s);
	}

	public static void main(String[] args) {
		ExampleApplication app = new ExampleApplication();
		app.run();
	}
}
